/*
	exec sp_update_allocatewip_for_backlog '265,266,'
*/
ALTER    proc sp_update_allocatewip_for_backlog @LINITEM_IDS VARCHAR(200)
AS
BEGIN
	DECLARE @SALESORDER_ID INT
	DECLARE @LINEITEM_NO INT
	DECLARE @PROCESSSTEP_ID BIGINT
	DECLARE @QTY_REQUESTED INT
	DECLARE @SCHEDULE_DATE DATETIME
	DECLARE @SCHEDULED_LINEITEMS_CURSOR CURSOR
	DECLARE @WIP_RESERVATION_CURSOR CURSOR
	DECLARE @AVAILABLE_QTY INT
	DECLARE @CURR_QTY_ALLOCATED INT
	DECLARE @LOT_ID INT
	DECLARE @LOT_QTY INT
	DECLARE @SALESORDER_NO VARCHAR(50)
	DECLARE @CUSTOMER_NAME VARCHAR(65)
	DECLARE @REQ_DATE DATETIME
	DECLARE @COOL_BOOKING_DATE DATETIME
	DECLARE @MFG_PART_NUMBER VARCHAR(50)
	DECLARE @RESERVED_LOT_CURSOR CURSOR
	
	DECLARE @RESULTS_TABLE TABLE ( SALESORDER_NO VARCHAR(50), LINEITEM_NO INT, CUSTOMER_NAME VARCHAR(50), MFG_PART_NUMBER VARCHAR(50), QTY_ALLOCATED INT, BOOKING_DATE DATETIME, REQ_DATE DATETIME, SCHEDULE_DATE DATETIME, REMARKS VARCHAR(500))

	IF ( @LINITEM_IDS IS NULL OR @LINITEM_IDS = '0' ) --ALL SCHEDULED LINE ITEMS
	 BEGIN
		SET @SCHEDULED_LINEITEMS_CURSOR = CURSOR FOR SELECT SALESORDER_ID, LINEITEM_NO, PROCESSSTEP_ID, QTY_ALLOCATED, SCHEDULE_DATE FROM BACKLOG_MASTER ORDER BY SCHEDULE_DATE ASC
		
		UPDATE WIP_MASTER SET QTY_ALLOCATED = 0
		DELETE FROM WIP_RESERVATION_TABLE
	 END
	ELSE --RESCHEDULING ALREADY SCHEDULED LINE ITEM USING LINE ITEM REVISED SCH DATE IF PRESENT OTHER WISE SCH DATE
	 BEGIN
		SET @SCHEDULED_LINEITEMS_CURSOR = CURSOR FOR SELECT BM.SALESORDER_ID, BM.LINEITEM_NO, BM.PROCESSSTEP_ID, BM.QTY_ALLOCATED, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE)
						FROM BACKLOG_MASTER BM, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE BM.SALESORDER_ID = COOL.SALESORDER_ID AND BM.LINEITEM_NO = COOL.COOL_LINE_ITEM_NO AND COOL.LINEITEM_ID IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING(@LINITEM_IDS) ) ORDER BY SCHEDULE_DATE ASC
		
		---RELEASING RESERVED QUANTITIES AND UPDATING SCHEDULE DATE IN BACKLOG_MASTER FOR THE GIVEN LINE ITEMS
		DECLARE @RESERVED_LOT_TABLE TABLE( LOT_ID INT, LOT_QTY INT, PROCESSSTEP_ID INT, SALESORDER_ID INT, COOL_LINE_ITEM_NO INT, SCHEDULE_DATE DATETIME)
		INSERT INTO @RESERVED_LOT_TABLE SELECT LOT_ID, QTY_ALLOCATED, P.PROCESSSTEP_ID, COOL.SALESORDER_ID, COOL.COOL_LINE_ITEM_NO, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) FROM WIP_RESERVATION_TABLE WRT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE WRT.SALESORDER_ID = COOL.SALESORDER_ID AND WRT.LINE_ITEM_NO = COOL.COOL_LINE_ITEM_NO AND P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.LINEITEM_ID IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING(@LINITEM_IDS) )

		SET @RESERVED_LOT_CURSOR = CURSOR FOR SELECT LOT_ID, LOT_QTY, PROCESSSTEP_ID, SALESORDER_ID, COOL_LINE_ITEM_NO, SCHEDULE_DATE FROM @RESERVED_LOT_TABLE
		OPEN @RESERVED_LOT_CURSOR
		FETCH NEXT FROM @RESERVED_LOT_CURSOR INTO @LOT_ID, @LOT_QTY, @PROCESSSTEP_ID, @SALESORDER_ID, @LINEITEM_NO, @SCHEDULE_DATE
		WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE WIP_MASTER SET QTY_ALLOCATED = QTY_ALLOCATED - ( ( ( FG_QTY * @LOT_QTY ) / ( SELECT SUM(FG_QTY) FROM WIP_MASTER WHERE FG_PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_ID = @LOT_ID ) ) ) WHERE LOT_ID = @LOT_ID
			UPDATE BACKLOG_MASTER SET SCHEDULE_DATE = @SCHEDULE_DATE WHERE SALESORDER_ID = @SALESORDER_ID AND LINEITEM_NO = @LINEITEM_NO
			DELETE FROM WIP_RESERVATION_TABLE WHERE SALESORDER_ID = @SALESORDER_ID AND LINE_ITEM_NO = @LINEITEM_NO
			
			SET @LOT_ID = NULL
			SET @LOT_QTY = 0
			SET @PROCESSSTEP_ID = NULL
			SET @SALESORDER_ID = NULL
			SET @LINEITEM_NO = NULL
			SET @SCHEDULE_DATE = NULL
			FETCH NEXT FROM @RESERVED_LOT_CURSOR INTO @LOT_ID, @LOT_QTY, @PROCESSSTEP_ID, @SALESORDER_ID, @LINEITEM_NO, @SCHEDULE_DATE
		END
		CLOSE @RESERVED_LOT_CURSOR
		DEALLOCATE @RESERVED_LOT_CURSOR
	 END

	OPEN @SCHEDULED_LINEITEMS_CURSOR 

	FETCH NEXT FROM @SCHEDULED_LINEITEMS_CURSOR INTO @SALESORDER_ID, @LINEITEM_NO, @PROCESSSTEP_ID, @QTY_REQUESTED, @SCHEDULE_DATE
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		--GETTING AVAILABLE QTY FOR THE SCHEDULED DATE
		SELECT @AVAILABLE_QTY = SUM( FG_QTY - QTY_ALLOCATED ) FROM WIP_MASTER, LOT_TABLE WHERE WIP_MASTER.LOT_ID = LOT_TABLE.LOT_ID AND LOT_TABLE.LOT_MANUAL_HOLD = 1 AND LOT_TABLE.LOT_CURRENT_STATUS <> 'HOLD' AND FG_PROCESSSTEP_ID = @PROCESSSTEP_ID AND FG_DATE <= @SCHEDULE_DATE
		IF ( @AVAILABLE_QTY IS NOT NULL AND @AVAILABLE_QTY >= @QTY_REQUESTED ) --QTY AVAILABLE FOR THE SCHEDULED DATE, NEED TO RESERVE THE LOTS
		 BEGIN
			SET @WIP_RESERVATION_CURSOR = CURSOR FOR 
				SELECT WIP_MASTER.LOT_ID, ( FG_QTY - QTY_ALLOCATED ) AS FG_QTY FROM WIP_MASTER, LOT_TABLE WHERE WIP_MASTER.LOT_ID = LOT_TABLE.LOT_ID AND LOT_TABLE.LOT_MANUAL_HOLD = 1 AND LOT_TABLE.LOT_CURRENT_STATUS <> 'HOLD' AND FG_PROCESSSTEP_ID = @PROCESSSTEP_ID AND FG_DATE <= @SCHEDULE_DATE ORDER BY FG_DATE DESC
			OPEN @WIP_RESERVATION_CURSOR
			FETCH NEXT FROM @WIP_RESERVATION_CURSOR INTO @LOT_ID, @LOT_QTY
			SET @CURR_QTY_ALLOCATED = 0
			WHILE @@FETCH_STATUS = 0
			 BEGIN
				IF ( (@QTY_REQUESTED-@CURR_QTY_ALLOCATED) <= @LOT_QTY )
				 BEGIN
					SET @LOT_QTY = @QTY_REQUESTED-@CURR_QTY_ALLOCATED
				 END
				
				INSERT INTO WIP_RESERVATION_TABLE( SALESORDER_ID, LINE_ITEM_NO, LOT_ID, QTY_ALLOCATED ) 
						VALUES ( @SALESORDER_ID, @LINEITEM_NO, @LOT_ID, @LOT_QTY )

				UPDATE WIP_MASTER SET QTY_ALLOCATED = QTY_ALLOCATED + ( ( ( FG_QTY * @LOT_QTY ) / ( SELECT SUM( FG_QTY ) FROM WIP_MASTER WHERE FG_PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOT_ID = @LOT_ID ) ) ) WHERE LOT_ID = @LOT_ID

				SET @CURR_QTY_ALLOCATED = @CURR_QTY_ALLOCATED + @LOT_QTY			
				SET @LOT_ID = NULL
				SET @LOT_QTY = 0
				IF ( @QTY_REQUESTED <= @CURR_QTY_ALLOCATED )
				 BEGIN
					SELECT @SALESORDER_NO = SALESORDER_NO, @CUSTOMER_NAME = C_NAME, @REQ_DATE = COOL_REQUESTED_DATE, @COOL_BOOKING_DATE = COOL_BOOKING_DATE, @MFG_PART_NUMBER = P.PART_NUMBER FROM SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, CUSTOMER C WHERE COOL.PRODUCT_ID = P.PRODUCT_ID AND SO.SALESORDER_ID = @SALESORDER_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND COOL.COOL_LINE_ITEM_NO = @LINEITEM_NO AND C.CUSTOMER_ID = SO.CUSTOMER_ID
					INSERT INTO @RESULTS_TABLE(SALESORDER_NO, LINEITEM_NO, CUSTOMER_NAME, MFG_PART_NUMBER, QTY_ALLOCATED, BOOKING_DATE, REQ_DATE, SCHEDULE_DATE, REMARKS )
							VALUES(@SALESORDER_NO, @LINEITEM_NO, @CUSTOMER_NAME, @MFG_PART_NUMBER, @QTY_REQUESTED, @COOL_BOOKING_DATE, @REQ_DATE, @SCHEDULE_DATE, 'SUCCESS' )
					BREAK
				 END
				FETCH NEXT FROM @WIP_RESERVATION_CURSOR INTO @LOT_ID, @LOT_QTY
			 END
			CLOSE @WIP_RESERVATION_CURSOR
			DEALLOCATE @WIP_RESERVATION_CURSOR
		 END
		ELSE -- QTY NOT AVAILABLE FOR THE SCHEDULED DATE
		 BEGIN
			SELECT @SALESORDER_NO = SALESORDER_NO, @CUSTOMER_NAME = C_NAME, @REQ_DATE = COOL_REQUESTED_DATE, @COOL_BOOKING_DATE = COOL_BOOKING_DATE, @MFG_PART_NUMBER = P.PART_NUMBER FROM SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, CUSTOMER C WHERE COOL.PRODUCT_ID = P.PRODUCT_ID AND SO.SALESORDER_ID = @SALESORDER_ID AND COOL.SALESORDER_ID = SO.SALESORDER_ID AND COOL.COOL_LINE_ITEM_NO = @LINEITEM_NO AND C.CUSTOMER_ID = SO.CUSTOMER_ID
			--GETTING AVAILABLE QTY
			SELECT @AVAILABLE_QTY = SUM( FG_QTY - QTY_ALLOCATED ) FROM WIP_MASTER, LOT_TABLE WHERE WIP_MASTER.LOT_ID = LOT_TABLE.LOT_ID AND LOT_TABLE.LOT_MANUAL_HOLD = 1 AND LOT_TABLE.LOT_CURRENT_STATUS <> 'HOLD' AND FG_PROCESSSTEP_ID = @PROCESSSTEP_ID
			IF ( @AVAILABLE_QTY IS NOT NULL AND @AVAILABLE_QTY >= @QTY_REQUESTED ) --QTY AVAILABLE BUT SCHEDULED DATE IS LESS
			 BEGIN
				INSERT INTO @RESULTS_TABLE(SALESORDER_NO, LINEITEM_NO, CUSTOMER_NAME, MFG_PART_NUMBER, QTY_ALLOCATED, BOOKING_DATE, REQ_DATE, SCHEDULE_DATE, REMARKS )
							VALUES(@SALESORDER_NO, @LINEITEM_NO, @CUSTOMER_NAME, @MFG_PART_NUMBER, @QTY_REQUESTED, @COOL_BOOKING_DATE, @REQ_DATE, @SCHEDULE_DATE, 'RESCHEDULE TO RESERVE THE LOTS' )
			 END
			ELSE --QTY NOT AVAILABLE TO RESERVE
			 BEGIN
				INSERT  INTO @RESULTS_TABLE(SALESORDER_NO, LINEITEM_NO, CUSTOMER_NAME, MFG_PART_NUMBER, QTY_ALLOCATED, BOOKING_DATE, REQ_DATE, SCHEDULE_DATE, REMARKS )
							VALUES(@SALESORDER_NO, @LINEITEM_NO, @CUSTOMER_NAME, @MFG_PART_NUMBER, @QTY_REQUESTED, @COOL_BOOKING_DATE, @REQ_DATE, @SCHEDULE_DATE, 'QTY NOT AVAILABLE TO RESERVE LOTS' )
			 END
		 END
		SET @SALESORDER_NO = NULL
		SET @CUSTOMER_NAME = NULL
		SET @REQ_DATE = NULL
		SET @COOL_BOOKING_DATE = NULL
		SET @MFG_PART_NUMBER = NULL
		SET @SALESORDER_ID = NULL
		SET @LINEITEM_NO = NULL
		SET @PROCESSSTEP_ID = NULL
		SET @QTY_REQUESTED = 0
		SET @SCHEDULE_DATE = NULL
		FETCH NEXT FROM @SCHEDULED_LINEITEMS_CURSOR INTO @SALESORDER_ID, @LINEITEM_NO, @PROCESSSTEP_ID, @QTY_REQUESTED, @SCHEDULE_DATE
	 END
	CLOSE @SCHEDULED_LINEITEMS_CURSOR
	DEALLOCATE @SCHEDULED_LINEITEMS_CURSOR

	SELECT * FROM @RESULTS_TABLE
END